# coding:utf-8

import os
import random
import set_env_path     # 请勿删除


from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

from app.api.constant import KF_PHONE_LIST

Base = declarative_base()
engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()


def generate_relation():
    """
    created by chenhj at 2017/11/15
    插入跳转到客服的虚拟号关系(20条)
    """
    select_virtual_num_sql = text("""
        select tel from number_pool_for_lt
         where for_sms is FALSE and use_count = 0 order by updated_at desc limit 20;
    """)

    insert_relation_sql = text("""
        insert into virtual_numbers (tel_a, tel_x, operator, created_at, updated_at)
          values (:tel_a, :tel_x, 2, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())
    """)

    update_virtual_num_sql = text("""
        update number_pool_for_lt set use_count = 1 where tel in :tel_list
    """)

    virtual_nums = session.execute(select_virtual_num_sql).fetchall()
    tel_list = []
    for each in virtual_nums:
        print(each.tel)
        tel_list.append(each.tel)
        session.execute(
            insert_relation_sql,
            {
                'tel_a': random.choice(KF_PHONE_LIST),
                'tel_x': each.tel
            }
        )
    session.execute(
        update_virtual_num_sql,
        {
            'tel_list': tuple(tel_list)
        }
    )
    session.commit()


if __name__ == '__main__':
    generate_relation()

